import openpyxl
from QuickTools import tools_pmc

wb = openpyxl.load_workbook(tools_pmc.lj_wlxxb)
ws = wb.worksheets[0]

dict_line = {}
for i in range(2, ws.max_row + 1):
    if str(ws.cell(row=i, column=1).value)[0:5] == "10310" or \
            str(ws.cell(row=i, column=1).value)[0:5] == "10309":
        if str(ws.cell(row=i, column=3).value) not in dict_line:
            dict_line[str(ws.cell(row=i, column=3).value)] = {
                ws.cell(row=i, column=1).value: ws.cell(row=i, column=14).value}

        elif str(ws.cell(row=i, column=1).value) not in dict_line[str(ws.cell(row=i, column=3).value)]:
            dict_line[str(ws.cell(row=i, column=3).value)][ws.cell(row=i, column=1).value] = ws.cell(row=i,
                                                                                                     column=14).value
        else:
            dict_line[str(ws.cell(row=i, column=3).value)][ws.cell(row=i, column=1).value] += ws.cell(row=i,
                                                                                                      column=14).value

wb = openpyxl.load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\today\线缆尾料消耗核查.xlsx")
ws = wb.worksheets[0]
for i in range(2, ws.max_row + 1):
    if ws.cell(row=i, column=15).value == "销货单" or ws.cell(row=i, column=15).value == "借出单":
        list_temp = list(dict_line[str(ws.cell(row=i, column=4).value)])
        list_temp.sort()
        if list_temp.index(ws.cell(row=i, column=1).value) != 0:
            for j in range(list_temp.index(ws.cell(row=i, column=1).value)):
                if dict_line[str(ws.cell(row=i, column=4).value)][list_temp[j]] > 0:
                    ws.cell(row=i, column=1).fill = tools_pmc.fill_red

wb.save(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\today\线缆尾料消耗核查2.xlsx")
